
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
  
  <title>Python 备份 豆瓣 读过的书的记录 | 何妨吟啸且徐行</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="之前总是担心某一天豆瓣撑不下去了会倒闭。于是，总想着把自己在豆瓣标记的读过的书，看过的电影都备份存下来，主要还有一些看完当时的短评记录。 电影的页面和读书的页面差不多的，所以这里就介绍一下备份读过的书的方法。最终存到Excel表格中和数据库中。">
<meta property="og:type" content="article">
<meta property="og:title" content="Python 备份 豆瓣 读过的书的记录">
<meta property="og:url" content="http://i007it.com/2021/02/20/Python-%E5%A4%87%E4%BB%BD-%E8%B1%86%E7%93%A3-%E8%AF%BB%E8%BF%87%E7%9A%84%E4%B9%A6%E7%9A%84%E8%AE%B0%E5%BD%95/index.html">
<meta property="og:site_name" content="何妨吟啸且徐行">
<meta property="og:description" content="之前总是担心某一天豆瓣撑不下去了会倒闭。于是，总想着把自己在豆瓣标记的读过的书，看过的电影都备份存下来，主要还有一些看完当时的短评记录。 电影的页面和读书的页面差不多的，所以这里就介绍一下备份读过的书的方法。最终存到Excel表格中和数据库中。">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2021-02-20T06:28:00.000Z">
<meta property="article:modified_time" content="2021-02-20T08:14:20.617Z">
<meta property="article:author" content="HDUZN hduzn@vip.qq.com">
<meta property="article:tag" content="Python">
<meta property="article:tag" content="Selenium">
<meta name="twitter:card" content="summary">
  
    <link rel="alternative" href="/atom.xml" title="何妨吟啸且徐行" type="application/atom+xml">
  
  
  
<link rel="stylesheet" href="/css/style.css">

  
    <link href="//fonts.useso.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <!--[if lt IE 9]><script src="//cdnjs.cloudflare.com/ajax/libs/html5shiv/3.7/html5shiv.min.js"></script><![endif]-->
  
  

</head>

<body>
<div id="container">
  <div id="wrap">
    <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <nav id="upper-nav" class="inner">
      <a id="main-nav-toggle" class="nav-icon"></a>
      <div class="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
        
        
          <a id="nav-github" class="nav-icon" target="_blank" rel="noopener" href="https://github.com/hduzn"></a>
        
      </div>
    </nav>
    <div id="header-title">
      
        <h1 id="blog-title-wrap">
          <a href="/" id="blog-title">一 蓑 烟 雨 任 平 生</a>
        </h1>
      
    </div>
    <div id="contenedor">
      <ul class="cube">
        <li class="cara">2022</li>
        <li class="cara">烟</li>
        <li class="cara">雨</li>
        <li class="cara">平</li>
        <li class="cara">生</li>
        <li class="cara">柒</li>
      </ul>
    </div>
    <nav id="main-nav">
      
        <a class="main-nav-link" href="/">首页</a>
      
        <a class="main-nav-link" href="/archives">归档</a>
      
        <a class="main-nav-link" href="/books">推荐阅读</a>
      
    </nav>
  </div>
</header>

    <div class="outer">
      <section id="main"><article id="post-Python-备份-豆瓣-读过的书的记录" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <h3 href="/2021/02/20/Python-%E5%A4%87%E4%BB%BD-%E8%B1%86%E7%93%A3-%E8%AF%BB%E8%BF%87%E7%9A%84%E4%B9%A6%E7%9A%84%E8%AE%B0%E5%BD%95/" class="article-date">
  <time datetime="2021-02-20T06:28:00.000Z" itemprop="datePublished">2021-02-20</time>
</h3>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/Python/">Python</a>
  </div>

  </div>
  <div class="article-inner">
  <div class="curve-down">
  <div class="fill-content">
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      Python 备份 豆瓣 读过的书的记录
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        
          <div id="toc" class="toc-article">
            <strong class="toc-title">文章目录</strong>
            <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%80%E3%80%81%E5%87%86%E5%A4%87%E5%B7%A5%E4%BD%9C"><span class="toc-text">一、准备工作</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E5%AE%89%E8%A3%85Selenium-%E6%B5%8B%E8%AF%95%E7%8E%AF%E5%A2%83"><span class="toc-text">1.安装Selenium 测试环境</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E5%AE%89%E8%A3%85-openpyxl-%E5%8C%85"><span class="toc-text">2.安装 openpyxl 包</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BA%8C%E3%80%81%E4%BB%A3%E7%A0%81%E5%AE%9E%E7%8E%B0"><span class="toc-text">二、代码实现</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E5%BB%BA%E7%AB%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E3%80%81%E8%A1%A8"><span class="toc-text">1.建立数据库、表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E8%8E%B7%E5%8F%96%E6%89%80%E6%9C%89%E9%A1%B5%E9%9D%A2%E7%9A%84%E9%93%BE%E6%8E%A5List-%E7%9A%84%E6%96%B9%E6%B3%95"><span class="toc-text">2.获取所有页面的链接List 的方法</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E8%8E%B7%E5%8F%96%E4%B8%80%E9%A1%B5%E7%9A%84%E5%86%85%E5%AE%B9%E6%96%B9%E6%B3%95"><span class="toc-text">3.获取一页的内容方法</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%89%E3%80%81%E6%95%B4%E4%B8%AA%E9%A1%B9%E7%9B%AE%E6%89%93%E5%8C%85"><span class="toc-text">三、整个项目打包</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-books-py"><span class="toc-text">1.books.py</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-douban-config-py-%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6"><span class="toc-text">2.douban_config.py 配置文件</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-z-db-py-%E6%93%8D%E4%BD%9C%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9B%B8%E5%85%B3%E6%96%B9%E6%B3%95"><span class="toc-text">3.z_db.py 操作数据库相关方法</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-douban-sql-%E5%88%9B%E5%BB%BAbooks%E8%A1%A8%E7%9A%84sql%E8%AF%AD%E5%8F%A5"><span class="toc-text">4.douban.sql 创建books表的sql语句</span></a></li></ol></li></ol>
          </div>
        
        <p>之前总是担心某一天豆瓣撑不下去了会倒闭。于是，总想着把自己在豆瓣标记的读过的书，看过的电影都备份存下来，主要还有一些看完当时的短评记录。</p>
<p>电影的页面和读书的页面差不多的，所以这里就介绍一下备份读过的书的方法。<br>最终存到Excel表格中和数据库中。</p>
<a id="more"></a>
<h2 id="一、准备工作"><a href="#一、准备工作" class="headerlink" title="一、准备工作"></a>一、准备工作</h2><h3 id="1-安装Selenium-测试环境"><a href="#1-安装Selenium-测试环境" class="headerlink" title="1.安装Selenium 测试环境"></a>1.安装Selenium 测试环境</h3><p>用来自动化操作豆瓣的页面，爬取数据。<br>之前有篇笔记写了：</p>
<p><a href="http://i007it.com/2021/02/08/Ubuntu%E5%AE%89%E8%A3%85Selenium%E8%87%AA%E5%8A%A8%E5%8C%96%E6%B5%8B%E8%AF%95/">Ubuntu 安装Selenium自动化测试</a></p>
<p>如果是Windows环境也是一样的，主要就是3步。</p>
<ul>
<li>安装Chrome浏览器 （Firefox浏览器）都可；</li>
<li>安装浏览器驱动（要对应浏览器的版本，就是一个chromedriver.exe这样的可执行文件）</li>
<li>安装Selenium包</li>
</ul>
<p>安装Selenium包Windows下命令为：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">pip install selenium</span><br></pre></td></tr></table></figure>
<h3 id="2-安装-openpyxl-包"><a href="#2-安装-openpyxl-包" class="headerlink" title="2.安装 openpyxl 包"></a>2.安装 openpyxl 包</h3><p>这个是用来操作Excel表的，把数据写入到Excel表里需要用到。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">pip install openpyxl</span><br></pre></td></tr></table></figure>
<h2 id="二、代码实现"><a href="#二、代码实现" class="headerlink" title="二、代码实现"></a>二、代码实现</h2><p><strong>思路</strong>：</p>
<ul>
<li>1).登录进豆瓣账号，点开【读过的书】的页面；</li>
<li>2).获取最大的页面数 max_page_num；</li>
<li>3).获取每个页面的链接，得到一个所有链接的List：page_site_list；</li>
<li>4).写一个获取一个页面信息的方法/函数：get_one_page，存成一个List；</li>
<li>4).循环page_site_list，读取每个页面的数据信息List存到全部数据的List中：all_page_data_list；</li>
<li>5).最后把全部数据 all_page_data_list 写入到Excel表中和存到数据库中。</li>
</ul>
<h3 id="1-建立数据库、表"><a href="#1-建立数据库、表" class="headerlink" title="1.建立数据库、表"></a>1.建立数据库、表</h3><p>建立一个数据库文件 <strong>douban.db</strong>，然后建一张存书信息的 <strong>books</strong>表。</p>
<p>里面有书籍的name(名称)，site(豆瓣链接)，author(作者)，tags(标签)，date(已读日期)，comments(评论)，rating(评分,星级)，pic(书籍图片链接)</p>
<p>存了书籍的图片链接，主要为了以后别的地方也可以用。比如博客里这个推荐阅读的页面：<a href="http://i007it.com/books/">推荐阅读</a><br>所以如果你不需要的信息，可以不存。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> &quot;books&quot; (</span><br><span class="line">	&quot;id&quot;	<span class="type">INTEGER</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">PRIMARY</span> <span class="keyword">KEY</span> AUTOINCREMENT <span class="keyword">UNIQUE</span>,</span><br><span class="line">	&quot;name&quot;	TEXT <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">	&quot;site&quot;	TEXT,</span><br><span class="line">	&quot;author&quot;	TEXT,</span><br><span class="line">	&quot;tags&quot;	TEXT,</span><br><span class="line">	&quot;date&quot;	TEXT,</span><br><span class="line">	&quot;comments&quot;	TEXT,</span><br><span class="line">	&quot;rating&quot;	TEXT,</span><br><span class="line">	&quot;pic&quot;	TEXT</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<h3 id="2-获取所有页面的链接List-的方法"><a href="#2-获取所有页面的链接List-的方法" class="headerlink" title="2.获取所有页面的链接List 的方法"></a>2.获取所有页面的链接List 的方法</h3><p>思路：<br>获取最大页面数的时候，把对应的链接存下来，这就是一个模板链接，model_site 如下：<br>其中<code>[id]</code>为每个人自己的豆瓣用户id。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">https:&#x2F;&#x2F;book.douban.com&#x2F;people&#x2F;[id]&#x2F;collect?start&#x3D;285&amp;sort&#x3D;time&amp;rating&#x3D;all&amp;filter&#x3D;all&amp;mode&#x3D;grid</span><br></pre></td></tr></table></figure>
<p>可以看到，<code>start=</code>后面的数字即是每个页面不同的地方。</p>
<ul>
<li>第1页：没写，其实是0；</li>
<li>第2页：15；</li>
<li>第3页：30；</li>
<li>第4页：45；</li>
<li>第n页：(n-1)*15</li>
</ul>
<p>所以可以把 model_site 切分成3段，数字前面是第一段，中间是页面相关的数字，第三段是 <code>&amp;sort</code>开始的后面这段。</p>
<p>第一段和第三段是固定的，通过一个循环就可以了。</p>
<p>代码实现如下：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">get_page_site_list</span>(<span class="params">max_page_num, model_site</span>):</span></span><br><span class="line">    <span class="comment"># model_site: https://book.douban.com/people/[id]/collect?start=285&amp;sort=time&amp;rating=all&amp;filter=all&amp;mode=grid</span></span><br><span class="line">    model_site_split1 = model_site.split(<span class="string">&#x27;=&#x27;</span>, <span class="number">1</span>)</span><br><span class="line">    site_part1 = model_site_split1[<span class="number">0</span>] + <span class="string">&#x27;=&#x27;</span></span><br><span class="line">    <span class="comment"># print(site_part1) # https://book.douban.com/people/[id]/collect?start=</span></span><br><span class="line">    model_site_split2 = model_site_split1[<span class="number">1</span>].split(<span class="string">&#x27;&amp;&#x27;</span>, <span class="number">1</span>)</span><br><span class="line">    <span class="comment"># site_part2_num = model_site_split2[0]</span></span><br><span class="line">    <span class="comment"># print(site_part2_num) # 285</span></span><br><span class="line">    site_part3 = <span class="string">&#x27;&amp;&#x27;</span> + model_site_split2[<span class="number">1</span>]</span><br><span class="line">    <span class="comment"># print(site_part3) # &amp;sort=time&amp;rating=all&amp;filter=all&amp;mode=grid</span></span><br><span class="line">    <span class="comment"># page_site = site_part1 + site_part2_num + site_part3</span></span><br><span class="line"></span><br><span class="line">    site_list = []</span><br><span class="line">    <span class="keyword">for</span> i <span class="keyword">in</span> <span class="built_in">range</span>(max_page_num):</span><br><span class="line">        site_part2_num = <span class="built_in">str</span>(i * <span class="number">15</span>)</span><br><span class="line">        page_site = site_part1 + site_part2_num + site_part3</span><br><span class="line">        site_list.append(page_site)</span><br><span class="line">    <span class="keyword">return</span> site_list</span><br></pre></td></tr></table></figure>
<h3 id="3-获取一页的内容方法"><a href="#3-获取一页的内容方法" class="headerlink" title="3.获取一页的内容方法"></a>3.获取一页的内容方法</h3><p>获取一页的内容，最后返回一个data_list。<br>data_list中每一项包括：书籍名、书籍链接、书籍作者信息、书籍标签、阅读日期、评论、评分、书籍图片链接。</p>
<p>代码实现：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 获取一页的内容，一共15项</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">get_one_page</span>(<span class="params">wd, page_site</span>):</span></span><br><span class="line">    <span class="keyword">try</span>:</span><br><span class="line">        wd.get(page_site)</span><br><span class="line">        time.sleep(<span class="number">2</span>)</span><br><span class="line">    <span class="keyword">except</span> selenium.common.exceptions.TimeoutException:</span><br><span class="line">        time.sleep(<span class="number">5</span>)</span><br><span class="line">        wd.get(page_site)</span><br><span class="line"></span><br><span class="line">    pic_list = []</span><br><span class="line">    pic_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .pic a img&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> pic_values:</span><br><span class="line">        pic_site = value.get_attribute(<span class="string">&#x27;src&#x27;</span>)</span><br><span class="line">        pic_list.append(pic_site)</span><br><span class="line">    <span class="comment"># print(pic_list)</span></span><br><span class="line"></span><br><span class="line">    book_site_list = []</span><br><span class="line">    book_name_list = []</span><br><span class="line">    book_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info h2 a&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> book_values:</span><br><span class="line">        book_site = value.get_attribute(<span class="string">&#x27;href&#x27;</span>)</span><br><span class="line">        book_site_list.append(book_site)</span><br><span class="line">        book_name = value.text</span><br><span class="line">        book_name_list.append(book_name)</span><br><span class="line">    <span class="comment"># print(book_site_list)</span></span><br><span class="line">    <span class="comment"># print(book_name_list)</span></span><br><span class="line"></span><br><span class="line">    author_list = []</span><br><span class="line">    author_info = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info .pub&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> author_info:</span><br><span class="line">        author_list.append(value.text)</span><br><span class="line">    <span class="comment"># print(author_list)</span></span><br><span class="line"></span><br><span class="line">    tags_list = []</span><br><span class="line">    tag_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info .tags&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> tag_values:</span><br><span class="line">        tags_list.append(value.text.split(<span class="string">&#x27;:&#x27;</span>)[<span class="number">1</span>].strip())</span><br><span class="line">    <span class="comment"># print(tags_list)</span></span><br><span class="line"></span><br><span class="line">    date_list = []</span><br><span class="line">    date_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info .date&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> date_values:</span><br><span class="line">        date_list.append(value.text.split()[<span class="number">0</span>])</span><br><span class="line">    <span class="comment"># print(date_list)</span></span><br><span class="line"></span><br><span class="line">    comment_list = []</span><br><span class="line">    comment_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info .comment&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> comment_values:</span><br><span class="line">        comment_list.append(value.text)</span><br><span class="line">    <span class="comment"># print(comment_list)</span></span><br><span class="line"></span><br><span class="line">    rating_list = []</span><br><span class="line">    rating_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info .short-note div span:nth-of-type(1)&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> rating_values:</span><br><span class="line">        rating_list.append(douban_config.rating_dict[value.get_attribute(<span class="string">&#x27;class&#x27;</span>)[<span class="number">6</span>]])</span><br><span class="line">    <span class="comment"># print(rating_list)</span></span><br><span class="line"></span><br><span class="line">    num = <span class="built_in">len</span>(book_name_list)</span><br><span class="line">    data_list = []</span><br><span class="line">    <span class="keyword">for</span> i <span class="keyword">in</span> <span class="built_in">range</span>(num):</span><br><span class="line">        data_list.append([book_name_list[i], book_site_list[i], author_list[i], tags_list[i], date_list[i], comment_list[i], rating_list[i], pic_list[i]])</span><br><span class="line">    <span class="keyword">return</span> data_list</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p><strong>注意</strong>：<br>因为每一页中是每一项取一个List、最后合成的。<br>所以如果书籍标记的时候，没有标签、没有评分(打星)的话，会报错，获取不到信息。</p>
<h2 id="三、整个项目打包"><a href="#三、整个项目打包" class="headerlink" title="三、整个项目打包"></a>三、整个项目打包</h2><p>整个项目放在 Github上了。链接： <a target="_blank" rel="noopener" href="https://github.com/hduzn/douban_backup">douban_backup</a></p>
<p>|– douban_backup<br>    |– db<br>        |– douban.db<br>    |– ex<br>        |– douban-模板.xlsx<br>    |– README.md<br>    |– books.py<br>    |– douban.sql<br>    |– douban_config.py<br>    |– z_db.py</p>
<h3 id="1-books-py"><a href="#1-books-py" class="headerlink" title="1.books.py"></a>1.books.py</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br><span class="line">209</span><br><span class="line">210</span><br><span class="line">211</span><br><span class="line">212</span><br><span class="line">213</span><br><span class="line">214</span><br><span class="line">215</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#!/usr/bin/env python</span></span><br><span class="line"><span class="comment"># -*- encoding: utf-8 -*-</span></span><br><span class="line"><span class="string">&#x27;&#x27;&#x27;</span></span><br><span class="line"><span class="string">@File    :   douban.py</span></span><br><span class="line"><span class="string">@Time    :   2021/02/18</span></span><br><span class="line"><span class="string">@Author  :   HDUZN</span></span><br><span class="line"><span class="string">@Version :   1.0</span></span><br><span class="line"><span class="string">@Contact :   hduzn@vip.qq.com</span></span><br><span class="line"><span class="string">@License :   (C)Copyright 2021-2022</span></span><br><span class="line"><span class="string">@Desc    :   将Douban 读过的书的记录保存到douban.xlsx 文件和数据库中</span></span><br><span class="line"><span class="string">             (注：豆瓣标记后，没写标签、没评分/星 会报错，没写评论没关系。)</span></span><br><span class="line"><span class="string">&#x27;&#x27;&#x27;</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># here put the import lib</span></span><br><span class="line"><span class="keyword">import</span> douban_config</span><br><span class="line"><span class="keyword">import</span> z_db</span><br><span class="line"><span class="keyword">import</span> time</span><br><span class="line"><span class="keyword">import</span> selenium</span><br><span class="line"><span class="keyword">from</span> selenium <span class="keyword">import</span> webdriver</span><br><span class="line"><span class="keyword">import</span> openpyxl</span><br><span class="line"><span class="keyword">import</span> os</span><br><span class="line"></span><br><span class="line"><span class="comment"># 获取最大页数和每一页的模板链接</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">get_max_page_num</span>(<span class="params">wd</span>):</span></span><br><span class="line">    num_element = wd.find_element_by_css_selector(<span class="string">&#x27;.paginator a:nth-last-of-type(1)&#x27;</span>)</span><br><span class="line">    max_page_num = <span class="built_in">int</span>(num_element.text)</span><br><span class="line">    site = num_element.get_attribute(<span class="string">&#x27;href&#x27;</span>)</span><br><span class="line">    num_site_list = [max_page_num, site]</span><br><span class="line">    <span class="keyword">return</span> num_site_list</span><br><span class="line"></span><br><span class="line"><span class="comment"># 获取所有页数的链接List</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">get_page_site_list</span>(<span class="params">max_page_num, model_site</span>):</span></span><br><span class="line">    <span class="comment"># model_site: https://book.douban.com/people/[id]/collect?start=285&amp;sort=time&amp;rating=all&amp;filter=all&amp;mode=grid</span></span><br><span class="line">    model_site_split1 = model_site.split(<span class="string">&#x27;=&#x27;</span>, <span class="number">1</span>)</span><br><span class="line">    site_part1 = model_site_split1[<span class="number">0</span>] + <span class="string">&#x27;=&#x27;</span></span><br><span class="line">    <span class="comment"># print(site_part1) # https://book.douban.com/people/[id]/collect?start=</span></span><br><span class="line">    model_site_split2 = model_site_split1[<span class="number">1</span>].split(<span class="string">&#x27;&amp;&#x27;</span>, <span class="number">1</span>)</span><br><span class="line">    <span class="comment"># site_part2_num = model_site_split2[0]</span></span><br><span class="line">    <span class="comment"># print(site_part2_num) # 285</span></span><br><span class="line">    site_part3 = <span class="string">&#x27;&amp;&#x27;</span> + model_site_split2[<span class="number">1</span>]</span><br><span class="line">    <span class="comment"># print(site_part3) # &amp;sort=time&amp;rating=all&amp;filter=all&amp;mode=grid</span></span><br><span class="line">    <span class="comment"># page_site = site_part1 + site_part2_num + site_part3</span></span><br><span class="line"></span><br><span class="line">    site_list = []</span><br><span class="line">    <span class="keyword">for</span> i <span class="keyword">in</span> <span class="built_in">range</span>(max_page_num):</span><br><span class="line">        site_part2_num = <span class="built_in">str</span>(i * <span class="number">15</span>)</span><br><span class="line">        page_site = site_part1 + site_part2_num + site_part3</span><br><span class="line">        site_list.append(page_site)</span><br><span class="line">    <span class="keyword">return</span> site_list</span><br><span class="line"></span><br><span class="line"><span class="comment"># 获取读过的书的页面 wd</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">get_readed_wd</span>(<span class="params">site</span>):</span></span><br><span class="line">    driver_path = douban_config.driver_path</span><br><span class="line">    wd = webdriver.Chrome(driver_path)</span><br><span class="line">    wd.implicitly_wait(<span class="number">10</span>)</span><br><span class="line"></span><br><span class="line">    wd.get(site)</span><br><span class="line">    time.sleep(<span class="number">2</span>)</span><br><span class="line">    <span class="comment"># 登录</span></span><br><span class="line">    wd.find_element_by_class_name(<span class="string">&#x27;top-nav-info&#x27;</span>).click()</span><br><span class="line">    wd.find_element_by_class_name(<span class="string">&#x27;account-tab-account&#x27;</span>).click()</span><br><span class="line">    account = wd.find_element_by_id(<span class="string">&#x27;username&#x27;</span>)</span><br><span class="line">    pwd = wd.find_element_by_id(<span class="string">&#x27;password&#x27;</span>)</span><br><span class="line">    login = wd.find_element_by_css_selector(<span class="string">&#x27;.account-form-field-submit a&#x27;</span>)</span><br><span class="line">    account.clear()</span><br><span class="line">    pwd.clear()</span><br><span class="line">    account.send_keys(douban_config.douban_id)</span><br><span class="line">    pwd.send_keys(douban_config.douban_pwd)</span><br><span class="line">    login.click()</span><br><span class="line">    time.sleep(<span class="number">5</span>)</span><br><span class="line"></span><br><span class="line">    <span class="comment"># 打开个人主页</span></span><br><span class="line">    wd.find_element_by_css_selector(<span class="string">&#x27;.nav-user-account a&#x27;</span>).click()</span><br><span class="line">    wd.find_element_by_css_selector(<span class="string">&#x27;.more-items tr a&#x27;</span>).click()</span><br><span class="line">    time.sleep(<span class="number">1</span>)</span><br><span class="line"></span><br><span class="line">    <span class="comment"># 读过（我这里只有想读、读过的记录，没有在读），如果有【在读】选项，type括号中的数字为(3)</span></span><br><span class="line">    readed = wd.find_element_by_css_selector(<span class="string">&#x27;#book h2 .pl a:nth-of-type(2)&#x27;</span>)</span><br><span class="line">    readed_site = readed.get_attribute(<span class="string">&#x27;href&#x27;</span>)</span><br><span class="line">    readed.click()</span><br><span class="line"></span><br><span class="line">    <span class="comment"># switch to new window 切换到新的窗口</span></span><br><span class="line">    <span class="keyword">for</span> handle <span class="keyword">in</span> wd.window_handles:</span><br><span class="line">        wd.switch_to.window(handle)</span><br><span class="line">        <span class="keyword">if</span>(wd.current_url == readed_site):</span><br><span class="line">            <span class="keyword">break</span></span><br><span class="line"></span><br><span class="line">    time.sleep(<span class="number">1</span>)</span><br><span class="line">    <span class="keyword">return</span> wd</span><br><span class="line"></span><br><span class="line"><span class="comment"># 获取一页的内容，一共15项</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">get_one_page</span>(<span class="params">wd, page_site</span>):</span></span><br><span class="line">    <span class="keyword">try</span>:</span><br><span class="line">        wd.get(page_site)</span><br><span class="line">        time.sleep(<span class="number">2</span>)</span><br><span class="line">    <span class="keyword">except</span> selenium.common.exceptions.TimeoutException:</span><br><span class="line">        time.sleep(<span class="number">5</span>)</span><br><span class="line">        wd.get(page_site)</span><br><span class="line"></span><br><span class="line">    pic_list = []</span><br><span class="line">    pic_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .pic a img&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> pic_values:</span><br><span class="line">        pic_site = value.get_attribute(<span class="string">&#x27;src&#x27;</span>)</span><br><span class="line">        pic_list.append(pic_site)</span><br><span class="line">    <span class="comment"># print(pic_list)</span></span><br><span class="line"></span><br><span class="line">    book_site_list = []</span><br><span class="line">    book_name_list = []</span><br><span class="line">    book_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info h2 a&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> book_values:</span><br><span class="line">        book_site = value.get_attribute(<span class="string">&#x27;href&#x27;</span>)</span><br><span class="line">        book_site_list.append(book_site)</span><br><span class="line">        book_name = value.text</span><br><span class="line">        book_name_list.append(book_name)</span><br><span class="line">    <span class="comment"># print(book_site_list)</span></span><br><span class="line">    <span class="comment"># print(book_name_list)</span></span><br><span class="line"></span><br><span class="line">    author_list = []</span><br><span class="line">    author_info = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info .pub&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> author_info:</span><br><span class="line">        author_list.append(value.text)</span><br><span class="line">    <span class="comment"># print(author_list)</span></span><br><span class="line"></span><br><span class="line">    tags_list = []</span><br><span class="line">    tag_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info .tags&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> tag_values:</span><br><span class="line">        tags_list.append(value.text.split(<span class="string">&#x27;:&#x27;</span>)[<span class="number">1</span>].strip())</span><br><span class="line">    <span class="comment"># print(tags_list)</span></span><br><span class="line"></span><br><span class="line">    date_list = []</span><br><span class="line">    date_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info .date&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> date_values:</span><br><span class="line">        date_list.append(value.text.split()[<span class="number">0</span>])</span><br><span class="line">    <span class="comment"># print(date_list)</span></span><br><span class="line"></span><br><span class="line">    comment_list = []</span><br><span class="line">    comment_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info .comment&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> comment_values:</span><br><span class="line">        comment_list.append(value.text)</span><br><span class="line">    <span class="comment"># print(comment_list)</span></span><br><span class="line"></span><br><span class="line">    rating_list = []</span><br><span class="line">    rating_values = wd.find_elements_by_css_selector(<span class="string">&#x27;.interest-list .subject-item .info .short-note div span:nth-of-type(1)&#x27;</span>)</span><br><span class="line">    <span class="keyword">for</span> value <span class="keyword">in</span> rating_values:</span><br><span class="line">        rating_list.append(douban_config.rating_dict[value.get_attribute(<span class="string">&#x27;class&#x27;</span>)[<span class="number">6</span>]])</span><br><span class="line">    <span class="comment"># print(rating_list)</span></span><br><span class="line"></span><br><span class="line">    num = <span class="built_in">len</span>(book_name_list)</span><br><span class="line">    data_list = []</span><br><span class="line">    <span class="keyword">for</span> i <span class="keyword">in</span> <span class="built_in">range</span>(num):</span><br><span class="line">        data_list.append([book_name_list[i], book_site_list[i], author_list[i], tags_list[i], date_list[i], comment_list[i], rating_list[i], pic_list[i]])</span><br><span class="line">    <span class="keyword">return</span> data_list</span><br><span class="line"></span><br><span class="line"><span class="comment"># 把记录数据List 全部存入数据库</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">insert_into_db</span>(<span class="params">db_file, table_name, data_list</span>):</span></span><br><span class="line">    columns = [<span class="string">&#x27;name&#x27;</span>, <span class="string">&#x27;site&#x27;</span>, <span class="string">&#x27;author&#x27;</span>, <span class="string">&#x27;tags&#x27;</span>, <span class="string">&#x27;date&#x27;</span>, <span class="string">&#x27;comments&#x27;</span>, <span class="string">&#x27;rating&#x27;</span>, <span class="string">&#x27;pic&#x27;</span>]</span><br><span class="line"></span><br><span class="line">    sql = z_db.get_insert_sql_by_colum_names(table_name, columns)</span><br><span class="line">    <span class="comment"># print(sql) # insert into books (name, site, author, tags, date, comments, rating, pic) values (?, ?, ?, ?, ?, ?, ?, ?)</span></span><br><span class="line">    z_db.insert_into_db(db_file, table_name, sql, data_list)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 把记录数据List 全部存入Excel表</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">write_to_excel</span>(<span class="params">ex_file, sheet_name, data_list</span>):</span></span><br><span class="line">    <span class="keyword">if</span>(os.path.exists(ex_file)):</span><br><span class="line">        book = openpyxl.load_workbook(ex_file)</span><br><span class="line">        <span class="keyword">if</span>(book[sheet_name]):</span><br><span class="line">            sheet = book[sheet_name]</span><br><span class="line">        <span class="keyword">else</span>:</span><br><span class="line">            sheet = book.create_sheet(sheet_name)</span><br><span class="line">    <span class="keyword">else</span>:</span><br><span class="line">        book = openpyxl.Workbook()</span><br><span class="line">        sheet = book.active</span><br><span class="line">        sheet.title = sheet_name</span><br><span class="line"></span><br><span class="line">    <span class="keyword">for</span> data_line <span class="keyword">in</span> data_list:</span><br><span class="line">        sheet.append(data_line)</span><br><span class="line">    book.save(ex_file)</span><br><span class="line"></span><br><span class="line"><span class="comment"># main</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">main</span>():</span></span><br><span class="line">    site = douban_config.books_site</span><br><span class="line">    wd = get_readed_wd(site)</span><br><span class="line"></span><br><span class="line">    num_site_list = get_max_page_num(wd)</span><br><span class="line">    max_page_num = num_site_list[<span class="number">0</span>]</span><br><span class="line">    print(max_page_num) <span class="comment"># 20</span></span><br><span class="line">    model_site = num_site_list[<span class="number">1</span>]</span><br><span class="line">    <span class="comment"># print(model_site) # https://book.douban.com/people/[id]/collect?start=285&amp;sort=time&amp;rating=all&amp;filter=all&amp;mode=grid</span></span><br><span class="line">    </span><br><span class="line">    page_site_list = get_page_site_list(max_page_num, model_site)</span><br><span class="line"></span><br><span class="line">    all_page_data_list = []</span><br><span class="line">    <span class="keyword">for</span> page_site <span class="keyword">in</span> page_site_list:</span><br><span class="line">        <span class="comment"># print(page_site)</span></span><br><span class="line">        print(<span class="string">&#x27;------------num: &#x27;</span>, page_site_list.index(page_site)+<span class="number">1</span>)</span><br><span class="line">        one_page_data_list = get_one_page(wd, page_site)</span><br><span class="line">        all_page_data_list = all_page_data_list + one_page_data_list</span><br><span class="line">    <span class="comment"># print(all_page_data_list)</span></span><br><span class="line">    wd.quit()</span><br><span class="line"></span><br><span class="line">    all_page_data_list.reverse() <span class="comment"># 倒序</span></span><br><span class="line"></span><br><span class="line">    db_file = douban_config.db_file</span><br><span class="line">    table_name = douban_config.books_table_name</span><br><span class="line">    insert_into_db(db_file, table_name, all_page_data_list)</span><br><span class="line">    time.sleep(<span class="number">3</span>)</span><br><span class="line"></span><br><span class="line">    ex_file = douban_config.ex_file</span><br><span class="line">    sheet_name = douban_config.book_sheet_name</span><br><span class="line">    write_to_excel(ex_file, sheet_name, all_page_data_list)</span><br><span class="line"></span><br><span class="line">    print(<span class="string">&#x27;--------------------- Douban 已读书目 记录保存成功！&#x27;</span>)</span><br><span class="line"></span><br><span class="line">main()</span><br><span class="line"><span class="comment"># z_db.delete_table(douban_config.db_file, douban_config.books_table_name) # 清空表</span></span><br></pre></td></tr></table></figure>
<h3 id="2-douban-config-py-配置文件"><a href="#2-douban-config-py-配置文件" class="headerlink" title="2.douban_config.py 配置文件"></a>2.douban_config.py 配置文件</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#!/usr/bin/env python</span></span><br><span class="line"><span class="comment"># -*- encoding: utf-8 -*-</span></span><br><span class="line"><span class="string">&#x27;&#x27;&#x27;</span></span><br><span class="line"><span class="string">@File    :   douban_config.py</span></span><br><span class="line"><span class="string">@Time    :   2021/02/18</span></span><br><span class="line"><span class="string">@Author  :   HDUZN</span></span><br><span class="line"><span class="string">@Version :   1.0</span></span><br><span class="line"><span class="string">@Contact :   hduzn@vip.qq.com</span></span><br><span class="line"><span class="string">@License :   (C)Copyright 2021-2022</span></span><br><span class="line"><span class="string">@Desc    :   config file</span></span><br><span class="line"><span class="string">&#x27;&#x27;&#x27;</span></span><br><span class="line"></span><br><span class="line">douban_id = <span class="string">&#x27;[豆瓣登录id(邮箱名)]&#x27;</span></span><br><span class="line">douban_pwd = <span class="string">&#x27;[豆瓣登录密码]&#x27;</span></span><br><span class="line"></span><br><span class="line">books_site = <span class="string">&#x27;https://book.douban.com&#x27;</span></span><br><span class="line">movies_site = <span class="string">&#x27;https://movie.douban.com&#x27;</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># windows</span></span><br><span class="line">driver_path = <span class="string">r&#x27;G:\Python\software\chromedriver.exe&#x27;</span></span><br><span class="line">db_file = <span class="string">r&#x27;G:\Python\code2\douban\db\douban.db&#x27;</span></span><br><span class="line">ex_file = <span class="string">r&#x27;G:\Python\code2\douban\ex\douban.xlsx&#x27;</span></span><br><span class="line"></span><br><span class="line">table_name_seq = <span class="string">&#x27;sqlite_sequence&#x27;</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># books.py</span></span><br><span class="line">books_table_name = <span class="string">&#x27;books&#x27;</span></span><br><span class="line">book_sheet_name = <span class="string">&#x27;books&#x27;</span></span><br><span class="line">rating_dict = &#123;</span><br><span class="line">    <span class="string">&#x27;5&#x27;</span>: <span class="string">&#x27;★★★★★ 力荐&#x27;</span>,</span><br><span class="line">    <span class="string">&#x27;4&#x27;</span>: <span class="string">&#x27;★★★★☆ 推荐&#x27;</span>,</span><br><span class="line">    <span class="string">&#x27;3&#x27;</span>: <span class="string">&#x27;★★★☆☆ 还行&#x27;</span>,</span><br><span class="line">    <span class="string">&#x27;2&#x27;</span>: <span class="string">&#x27;★★☆☆☆ 较差&#x27;</span>,</span><br><span class="line">    <span class="string">&#x27;1&#x27;</span>: <span class="string">&#x27;★☆☆☆☆ 很差&#x27;</span></span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h3 id="3-z-db-py-操作数据库相关方法"><a href="#3-z-db-py-操作数据库相关方法" class="headerlink" title="3.z_db.py 操作数据库相关方法"></a>3.z_db.py 操作数据库相关方法</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#!/usr/bin/env python</span></span><br><span class="line"><span class="comment"># -*- encoding: utf-8 -*-</span></span><br><span class="line"><span class="string">&#x27;&#x27;&#x27;</span></span><br><span class="line"><span class="string">@File    :   z_db.py</span></span><br><span class="line"><span class="string">@Time    :   2021/02/10</span></span><br><span class="line"><span class="string">@Author  :   HDUZN</span></span><br><span class="line"><span class="string">@Version :   1.0</span></span><br><span class="line"><span class="string">@Contact :   hduzn@vip.qq.com</span></span><br><span class="line"><span class="string">@License :   (C)Copyright 2021-2022</span></span><br><span class="line"><span class="string">@Desc    :   操作数据库</span></span><br><span class="line"><span class="string">&#x27;&#x27;&#x27;</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># here put the import lib</span></span><br><span class="line"><span class="keyword">import</span> sqlite3</span><br><span class="line"><span class="keyword">import</span> douban_config</span><br><span class="line"></span><br><span class="line"><span class="comment"># 清空 table_name表中数据，并且将自增量变为0</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">delete_table</span>(<span class="params">db_file, table_name</span>):</span></span><br><span class="line">    conn = sqlite3.connect(db_file)</span><br><span class="line">    cursor = conn.cursor()</span><br><span class="line">    table_name_seq = douban_config.table_name_seq</span><br><span class="line"></span><br><span class="line">    <span class="comment"># 清空 table_name 表中数据</span></span><br><span class="line">    sql1 = <span class="string">&#x27;delete from &#x27;</span> + table_name</span><br><span class="line">    cursor.execute(sql1)</span><br><span class="line">    <span class="comment"># 把 table_name 表的自增列序号变为0</span></span><br><span class="line">    sql2 = <span class="string">&#x27;update &#x27;</span> + table_name_seq + <span class="string">&#x27; set seq = 0 where name = &#x27;</span> + <span class="string">&quot;&#x27;&quot;</span> + table_name + <span class="string">&quot;&#x27;&quot;</span></span><br><span class="line">    <span class="comment"># sql2 = update sqlite_sequence set seq = 0 where name = &#x27;hushen_300&#x27;</span></span><br><span class="line">    cursor.execute(sql2)</span><br><span class="line"></span><br><span class="line">    cursor.close()</span><br><span class="line">    conn.commit()</span><br><span class="line">    conn.close()</span><br><span class="line"></span><br><span class="line"><span class="comment"># 获取 insert sql语句 e.g insert into funds_dict (code, name, site) values (?, ?, ?)</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">get_insert_sql_by_colum_names</span>(<span class="params">table_name, col_name_list</span>):</span></span><br><span class="line">    sql1 = <span class="string">&#x27; (&#x27;</span></span><br><span class="line">    sql2 = <span class="string">&#x27; (&#x27;</span></span><br><span class="line">    <span class="keyword">for</span> col <span class="keyword">in</span> col_name_list:</span><br><span class="line">        sql1 = sql1 + col</span><br><span class="line">        <span class="keyword">if</span>(col_name_list.index(col) == (<span class="built_in">len</span>(col_name_list)-<span class="number">1</span>)): <span class="comment"># 判断col_name_list列表最后一个元素</span></span><br><span class="line">            sql1 = sql1 + <span class="string">&#x27;) &#x27;</span></span><br><span class="line">            sql2 = sql2 + <span class="string">&#x27;?)&#x27;</span></span><br><span class="line">            <span class="keyword">break</span></span><br><span class="line">        <span class="keyword">else</span>:</span><br><span class="line">            sql1 = sql1 + <span class="string">&#x27;, &#x27;</span></span><br><span class="line">            sql2 = sql2 + <span class="string">&#x27;?, &#x27;</span></span><br><span class="line">    <span class="comment"># print(sql1) # e.g  (code, name, site)</span></span><br><span class="line">    <span class="comment"># print(sql2) # e.g (?, ?, ?)</span></span><br><span class="line">    sql = <span class="string">&#x27;insert into &#x27;</span> + table_name + sql1 + <span class="string">&#x27;values&#x27;</span> + sql2 <span class="comment"># e.g insert into test (code, name, site) values (?, ?, ?)</span></span><br><span class="line"></span><br><span class="line">    <span class="keyword">return</span> sql</span><br><span class="line"></span><br><span class="line"><span class="comment"># insert list into table e.g data_list = [[&#x27;1&#x27;,&#x27;a&#x27;], [&#x27;2&#x27;,&#x27;b&#x27;]]</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">insert_into_db</span>(<span class="params">db_file, table_name, sql, data_list</span>):</span></span><br><span class="line">    conn = sqlite3.connect(db_file)</span><br><span class="line">    cursor = conn.cursor()</span><br><span class="line"></span><br><span class="line">    <span class="keyword">for</span> data_line <span class="keyword">in</span> data_list:</span><br><span class="line">        cursor.execute(sql, <span class="built_in">tuple</span>(data_line))</span><br><span class="line"></span><br><span class="line">    cursor.close()</span><br><span class="line">    conn.commit()</span><br><span class="line">    conn.close()</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h3 id="4-douban-sql-创建books表的sql语句"><a href="#4-douban-sql-创建books表的sql语句" class="headerlink" title="4.douban.sql 创建books表的sql语句"></a>4.douban.sql 创建books表的sql语句</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> &quot;books&quot; (</span><br><span class="line">	&quot;id&quot;	<span class="type">INTEGER</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">PRIMARY</span> <span class="keyword">KEY</span> AUTOINCREMENT <span class="keyword">UNIQUE</span>,</span><br><span class="line">	&quot;name&quot;	TEXT <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">	&quot;site&quot;	TEXT,</span><br><span class="line">	&quot;author&quot;	TEXT,</span><br><span class="line">	&quot;tags&quot;	TEXT,</span><br><span class="line">	&quot;date&quot;	TEXT,</span><br><span class="line">	&quot;comments&quot;	TEXT,</span><br><span class="line">	&quot;rating&quot;	TEXT,</span><br><span class="line">	&quot;pic&quot;	TEXT</span><br><span class="line">);</span><br><span class="line"></span><br></pre></td></tr></table></figure>
      
    </div>
    <footer class="article-footer">
      <div class="article-footer-content">
        
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/Python/" rel="tag">Python</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/Selenium/" rel="tag">Selenium</a></li></ul>

        <a data-url="http://i007it.com/2021/02/20/Python-%E5%A4%87%E4%BB%BD-%E8%B1%86%E7%93%A3-%E8%AF%BB%E8%BF%87%E7%9A%84%E4%B9%A6%E7%9A%84%E8%AE%B0%E5%BD%95/" data-id="cl1t8bgmn003o54ep1f9je15s" class="article-share-link">分享到</a>
        
        
          <a href="/2021/02/20/Python-%E5%A4%87%E4%BB%BD-%E8%B1%86%E7%93%A3-%E8%AF%BB%E8%BF%87%E7%9A%84%E4%B9%A6%E7%9A%84%E8%AE%B0%E5%BD%95/#comments" class="article-comment-link">
            <span class="post-comments-count valine-comment-count" data-xid="/2021/02/20/Python-%E5%A4%87%E4%BB%BD-%E8%B1%86%E7%93%A3-%E8%AF%BB%E8%BF%87%E7%9A%84%E4%B9%A6%E7%9A%84%E8%AE%B0%E5%BD%95/" itemprop="commentCount"></span>
            文章评论
          </a>
        
      </div>
    </footer>
  </div>
  </div>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2021/03/13/Python%E5%AF%B9%E5%9B%BE%E7%89%87%E8%BF%9B%E8%A1%8C%E6%96%87%E5%AD%97%E8%AF%86%E5%88%AB/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">下一篇</strong>
      <div class="article-nav-title">
        
          Python 对图片进行文字识别
        
      </div>
    </a>
  
  
    <a href="/2021/02/19/Python-selenium-%E5%88%87%E6%8D%A2%E5%88%B0%E6%96%B0%E7%9A%84%E7%AA%97%E5%8F%A3%E6%96%B9%E6%B3%95/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">上一篇</strong>
      <div class="article-nav-title">Python selenium 切换到新的窗口方法</div>
    </a>
  
</nav>

  
</article>


  
    <section id="comments" class="vcomment">
  
    </section>
  
</section>
      
      <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">近期文章</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2022/04/10/Flask-SQLAlchemy%E6%B5%AE%E7%82%B9%E6%95%B0%E7%B1%BB%E5%9E%8BFloat%E4%B8%A2%E5%A4%B1%E7%B2%BE%E5%BA%A6%E8%A7%A3%E5%86%B3%E6%96%B9%E6%B3%95/">Flask SQLAlchemy 浮点数类型Float 丢失精度 解决方法</a>
          </li>
        
          <li>
            <a href="/2022/04/09/iphone%E8%BF%9B%E5%85%A5DFU%E6%A8%A1%E5%BC%8F%E6%96%B9%E6%B3%95/">iphone进入DFU模式方法</a>
          </li>
        
          <li>
            <a href="/2022/04/09/u%E7%9B%98%E5%90%AF%E5%8A%A8%E8%BF%9B%E5%85%A5PE%E7%B3%BB%E7%BB%9F%E5%BF%AB%E6%8D%B7%E9%94%AE/">U盘启动进入PE系统 快捷键</a>
          </li>
        
          <li>
            <a href="/2022/04/09/you-get%20%E4%BD%BF%E7%94%A8%E6%95%99%E7%A8%8B/">you-get 使用教程</a>
          </li>
        
          <li>
            <a href="/2022/04/09/youtube-dl%20%E4%BD%BF%E7%94%A8%E6%95%99%E7%A8%8B/">youtube-dl 使用教程</a>
          </li>
        
      </ul>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">标签</h3>
    <div class="widget">
      <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/tags/Chrome/" rel="tag">Chrome</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Docker/" rel="tag">Docker</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Flask/" rel="tag">Flask</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Github/" rel="tag">Github</a><span class="tag-list-count">4</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Hexo/" rel="tag">Hexo</a><span class="tag-list-count">5</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Linux/" rel="tag">Linux</a><span class="tag-list-count">5</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Markdown/" rel="tag">Markdown</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/MySQL/" rel="tag">MySQL</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Navicat/" rel="tag">Navicat</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/OS/" rel="tag">OS</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/PicGo/" rel="tag">PicGo</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Python/" rel="tag">Python</a><span class="tag-list-count">10</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/SQLite/" rel="tag">SQLite</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Selenium/" rel="tag">Selenium</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Software/" rel="tag">Software</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Typora/" rel="tag">Typora</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Ubuntu/" rel="tag">Ubuntu</a><span class="tag-list-count">5</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/iphone/" rel="tag">iphone</a><span class="tag-list-count">1</span></li></ul>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">归档</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2022/04/">四月 2022</a><span class="archive-list-count">11</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/03/">三月 2021</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/02/">二月 2021</a><span class="archive-list-count">16</span></li></ul>
    </div>
  </div>

  
</aside>
      
    </div>
    <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2022 HDUZN hduzn@vip.qq.com<br>
      Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>
      .
      Theme by <a href="https://github.com/sun11/hexo-theme-paperbox" target="_blank">Paperbox</a>
    </div>
  </div>
</footer>
  </div>
  <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">首页</a>
  
    <a href="/archives" class="mobile-nav-link">归档</a>
  
    <a href="/books" class="mobile-nav-link">推荐阅读</a>
  
  <a href="#search" class="mobile-nav-link st-search-show-outputs">搜索</a>
</nav>

  
<script type="text/x-mathjax-config">
  MathJax.Hub.Config({
    menuSettings: {
      zoom: "None"
    },
    showMathMenu: false,
    jax: ["input/TeX","output/CommonHTML"],
    extensions: ["tex2jax.js"],
    TeX: {
      extensions: ["AMSmath.js","AMSsymbols.js"],
      equationNumbers: {
        autoNumber: "AMS"
      }
    },
  });
</script>

<script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/mathjax/2.6.1/MathJax.js"></script>



<!-- totop start -->
<div id="totop">
	<a title="返回顶部"></a>
</div>
<!-- totop end -->

<!-- swiftype search start -->

<!-- swiftype search end -->



<!-- valine start -->

    
<script src="https://cdn.jsdelivr.net/npm/valine@1.3.10/dist/Valine.min.js"></script>

  <script>
      var GUEST_INFO = ['nick','mail','link'];
      var guest_info = 'nick,mail,link'.split(',').filter(function(item){
          return GUEST_INFO.indexOf(item) > -1
      });
      var notify = 'false' == true;
      var verify = 'false' == true;
      new Valine({
          el: '.vcomment',
          notify: notify,
          verify: verify,
          appId: "UVs2AheLuqJbRc85LQv7vzMz-gzGzoHsz",
          appKey: "y98dsYB3ugs25U2vgNWTb3m7",
          placeholder: "请在此输入您的留言",
          pageSize:'10',
          avatar:'mm',
          lang:'zh-cn'
      });
  </script>
  

<!-- valine end -->

<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>

<script src="//cdnjs.cloudflare.com/ajax/libs/lrsjng.jquery-qrcode/0.12.0/jquery.qrcode.min.js"></script>




<script src="/js/script.js"></script>


</div>
</body>
</html>
